#!/usr/bin/env python
# coding=utf-8
#　将两台服务器S1S2的os层数据写入数据库

import MySQLdb
import os
import json


def os():
	# 连接数据库
	conn = MySQLdb.connect(
	                host='localhost',
	                port=3306,
	                user='root',
	                passwd='cnv6201',
	                db='PRESENTATION',
	                charset='utf8',
	            )
	cur = conn.cursor()
	# 清空数据库中表格内的信息
	cur.execute("delete from presentation_s1_os_before")
	cur.execute("delete from presentation_s1_os_after")
	cur.execute("delete from presentation_s2_os_before")
	cur.execute("delete from presentation_s2_os_after")

	# 读取网络状态信息
	f = open('JsonFileReport/report_os_S1_before.json', "r", encoding='UTF-8')
	n = json.load(f)
	for i in range(len(n['layer_info']['VMs'])):
	    if n['layer_info']['VMs'][i]['state'] == "ON":
	        os_kind = n['layer_info']['VMs'][i]['ostype']
	        os_version = n['layer_info']['VMs'][i]['osversion']
	        os_bit = n['layer_info']['VMs'][i]['osbit']
	        vmname = n['layer_info']['VMs'][i]['vmname']
	        cur.execute(
	            "insert into presentation_s1_os_before(os_kind, os_version, os_bit, vmname) values(%s,%s,%s,%s)",
	            [os_kind, os_version, os_bit, vmname])
	f.close()

	f = open('JsonFileReport/report_os_S1_after.json', "r", encoding='UTF-8')
	n = json.load(f)
	for i in range(len(n['layer_info']['VMs'])):
	    if n['layer_info']['VMs'][i]['state'] == "ON":
	        os_kind = n['layer_info']['VMs'][i]['ostype']
	        os_version = n['layer_info']['VMs'][i]['osversion']
	        os_bit = n['layer_info']['VMs'][i]['osbit']
	        vmname = n['layer_info']['VMs'][i]['vmname']
	        cur.execute(
	            "insert into presentation_s1_os_after(os_kind, os_version, os_bit, vmname) values(%s,%s,%s,%s)",
	            [os_kind, os_version, os_bit, vmname])
	f.close()

	f = open('JsonFileReport/report_os_S2_before.json', "r", encoding='UTF-8')
	n = json.load(f)
	for i in range(len(n['layer_info']['VMs'])):
	    if n['layer_info']['VMs'][i]['state'] == "ON":
	        os_kind = n['layer_info']['VMs'][i]['ostype']
	        os_version = n['layer_info']['VMs'][i]['osversion']
	        os_bit = n['layer_info']['VMs'][i]['osbit']
	        vmname = n['layer_info']['VMs'][i]['vmname']
	        cur.execute(
	            "insert into presentation_s2_os_before(os_kind, os_version, os_bit, vmname) values(%s,%s,%s,%s)",
	            [os_kind, os_version, os_bit, vmname])
	f.close()

	f = open('JsonFileReport/report_os_S2_after.json', "r", encoding='UTF-8')
	n = json.load(f)
	for i in range(len(n['layer_info']['VMs'])):
	    if n['layer_info']['VMs'][i]['state'] == "ON":
	        os_kind = n['layer_info']['VMs'][i]['ostype']
	        os_version = n['layer_info']['VMs'][i]['osversion']
	        os_bit = n['layer_info']['VMs'][i]['osbit']
	        vmname = n['layer_info']['VMs'][i]['vmname']
	        cur.execute(
	            "insert into presentation_s2_os_after(os_kind, os_version, os_bit, vmname) values(%s,%s,%s,%s)",
	            [os_kind, os_version, os_bit, vmname])
	f.close()

	cur.close()
	conn.commit()
	conn.close()
	print("os      层数据存储成功")
